package query.select

import ast.expr.*
import ast.limit.SqlLimit
import ast.order.SqlOrderBy
import ast.order.SqlOrderByOption
import ast.statement.select.SqlSelect
import ast.statement.select.SqlSelectQuery
import ast.table.*
import database.DBConnection
import dsl.column
import dsl.count
import expr.*
import query.Page
import util.toSqlString
import visitor.checkOLAP
import visitor.getExpr
import visitor.getQueryExpr
import java.math.BigDecimal
import java.sql.Connection
import java.sql.Date
import java.sql.SQLException

class Select(
    var db: DB = DB.MYSQL,
    override var conn: Connection? = null,
    override var isTransaction: Boolean = false,
    override var dbConnection: DBConnection? = null
) :
    SelectQueryImpl() {
    constructor(db: DB) : this(db, null, false, null)

    private var sqlSelect = SqlSelect()

    private lateinit var joinLeft: SqlTableSource

    init {
        sqlSelect.addSelectItem(SqlAllColumnExpr())
    }

    fun getSqlSelect() = this.sqlSelect

    fun from(table: String, alias: String? = null): Select {
        val from = SqlIdentifierTableSource(table)
        from.alias = alias
        joinLeft = from
        sqlSelect.from = from
        return this
    }

    infix fun from(table: String): Select {
        return from(table, null)
    }

    fun from(select: SelectQuery, alias: String? = null): Select {
        val from = SqlSubQueryTableSource(select.getSelect(), alias)
        joinLeft = from
        sqlSelect.from = from
        return this
    }

    infix fun from(select: SelectQuery): Select {
        return from(select, null)
    }

    infix fun fromLateral(select: SelectQuery): Select {
        val from = SqlSubQueryTableSource(select.getSelect(), isLateral = true)
        joinLeft = from
        sqlSelect.from = from
        return this
    }

    fun <T : TableSchema> from(table: T, alias: String? = null): Select {
        return from(table.tableName, alias)
    }

    infix fun <T : TableSchema> from(table: T): Select {
        return from(table.tableName, null)
    }

    infix fun alias(name: String): Select {
        val from = this.sqlSelect.from
        if (from is SqlJoinTableSource) {
            from.right.alias = name
        } else {
            from?.alias = name
        }

        return this
    }

    fun alias(tableName: String, vararg columnName: String): Select {
        val from = this.sqlSelect.from
        if (from is SqlJoinTableSource) {
            from.right.alias = tableName
            from.right.columnAliasNames.addAll(columnName.toList())
        } else {
            from?.alias = tableName
            from?.columnAliasNames?.addAll(columnName.toList())
        }

        return this
    }

    fun distinct(): Select {
        sqlSelect.distinct = true
        return this
    }

    fun select(vararg query: Query): Select {
        if (this.sqlSelect.selectList.size == 1 && this.sqlSelect.selectList[0].expr is SqlAllColumnExpr) {
            this.sqlSelect.selectList.clear()
        }
        query.forEach {
            val queryExpr = getQueryExpr(it, this.db)
            sqlSelect.addSelectItem(queryExpr.expr, queryExpr.alias)
        }
        return this
    }

    infix operator fun invoke(query: List<Query>): Select {
        return select(*query.toTypedArray())
    }

    infix operator fun invoke(query: Query): Select {
        return select(*arrayOf(query))
    }

    infix fun select(query: List<Query>): Select {
        return select(*query.toTypedArray())
    }

    infix fun select(query: Query): Select {
        return select(*arrayOf(query))
    }

    infix fun selectDistinct(query: List<Query>): Select {
        distinct()
        return select(*query.toTypedArray())
    }

    infix fun selectDistinct(query: Query): Select {
        distinct()
        return select(*arrayOf(query))
    }

    fun select(): Select {
        if (this.sqlSelect.selectList.size == 1 && this.sqlSelect.selectList[0].expr is SqlAllColumnExpr) {
            this.sqlSelect.selectList.clear()
        }
        sqlSelect.addSelectItem(SqlAllColumnExpr())
        return this
    }

    fun select(vararg columns: String): Select {
        if (this.sqlSelect.selectList.size == 1 && this.sqlSelect.selectList[0].expr is SqlAllColumnExpr) {
            this.sqlSelect.selectList.clear()
        }
        columns.forEach {
            val trim = it.trim()

            val split = trim.split(" ")
            val alias = if (trim.contains(" ")) {
                split.last()
            } else {
                null
            }
            val column = split.first()

            if (column.contains(".")) {
                val splitDot = column.split(".")
                val expr = if (splitDot.last().contains("*")) {
                    SqlAllColumnExpr(splitDot.first())
                } else {
                    SqlPropertyExpr(splitDot.first(), splitDot.last())
                }
                sqlSelect.addSelectItem(expr, alias)
            } else {
                val expr = if (column.contains("*")) {
                    SqlAllColumnExpr()
                } else {
                    SqlIdentifierExpr(column)
                }
                sqlSelect.addSelectItem(expr, alias)
            }
        }
        return this
    }

    infix fun where(query: Query): Select {
        sqlSelect.addCondition(getQueryExpr(query, this.db).expr)
        return this
    }

    fun where(test: () -> Boolean, query: Query): Select {
        if (test()) {
            where(query)
        }
        return this
    }

    fun where(test: Boolean, query: Query): Select {
        if (test) {
            where(query)
        }
        return this
    }

    infix fun having(query: Query): Select {
        sqlSelect.addHaving(getQueryExpr(query, this.db).expr)
        return this
    }

    private fun orderBy(specification: SqlOrderByOption, vararg columns: Query) {
        columns.forEach {
            val item = SqlOrderBy(getQueryExpr(it, this.db).expr, specification)
            sqlSelect.orderBy.add(item)
        }
    }

    fun orderByAsc(vararg columns: String): Select {
        orderBy(SqlOrderByOption.ASC, *columns.map { column(it) }.toTypedArray())
        return this
    }

    fun orderByAsc(vararg columns: Query): Select {
        orderBy(SqlOrderByOption.ASC, *columns)
        return this
    }

    infix fun orderByAsc(columns: List<Query>): Select {
        return orderByAsc(*columns.toTypedArray())
    }

    infix fun orderByAsc(column: Query): Select {
        return orderByAsc(*arrayOf(column))
    }

    fun orderByDesc(vararg columns: String): Select {
        orderBy(SqlOrderByOption.DESC, *columns.map { column(it) }.toTypedArray())
        return this
    }

    fun orderByDesc(vararg columns: Query): Select {
        orderBy(SqlOrderByOption.DESC, *columns)
        return this
    }

    infix fun orderByDesc(columns: List<Query>): Select {
        return orderByDesc(*columns.toTypedArray())
    }

    infix fun orderByDesc(column: Query): Select {
        return orderByDesc(*arrayOf(column))
    }

    fun orderBy(vararg orderBy: OrderBy): Select {
        orderBy.forEach {
            orderBy(it.order, it.query)
        }
        return this
    }

    infix fun orderBy(orderByList: List<OrderBy>): Select {
        return orderBy(*orderByList.toTypedArray())
    }

    infix fun orderBy(orderBy: OrderBy): Select {
        return orderBy(*arrayOf(orderBy))
    }

    fun limit(count: Int, offset: Int): Select {
        val sqlLimit = SqlLimit(count, offset)
        sqlSelect.limit = sqlLimit
        return this
    }

    infix fun limit(count: Int): Select {
        return limit(count, 0)
    }

    infix fun offset(offset: Int): Select {
        this.sqlSelect.limit?.offset = offset
        return this
    }

    fun groupBy(vararg columns: Query): Select {
        columns.forEach {
            val expr = getQueryExpr(it, this.db).expr
            sqlSelect.groupBy.add(expr)
        }
        return this
    }

    infix fun groupBy(column: Query): Select {
        return groupBy(*arrayOf(column))
    }

    infix fun groupBy(columns: List<Query>): Select {
        return groupBy(*columns.toTypedArray())
    }

    fun groupBy(vararg columns: String): Select {
        val query = columns.map { QueryColumn(it) }.toTypedArray()
        return groupBy(*query)
    }

    private fun join(
        table: String,
        joinType: SqlJoinType
    ): Select {
        val join = SqlJoinTableSource(joinLeft, joinType, SqlIdentifierTableSource(table))
        sqlSelect.from = join
        joinLeft = join
        return this
    }

    private fun join(
        table: SelectQuery,
        joinType: SqlJoinType,
        isLateral: Boolean = false
    ): Select {
        val join =
            SqlJoinTableSource(joinLeft, joinType, SqlSubQueryTableSource(table.getSelect(), isLateral = isLateral))
        sqlSelect.from = join
        joinLeft = join
        return this
    }

    infix fun on(on: Query): Select {
        val from = this.sqlSelect.from
        if (from is SqlJoinTableSource) {
            from.on = getQueryExpr(on, this.db).expr
        }
        return this
    }

    infix fun join(table: String): Select {
        return join(table, SqlJoinType.JOIN)
    }

    infix fun join(table: TableSchema): Select {
        return join(table.tableName, SqlJoinType.JOIN)
    }

    infix fun join(table: SelectQuery): Select {
        return join(table, SqlJoinType.JOIN)
    }

    infix fun joinLateral(table: SelectQuery): Select {
        return join(table, SqlJoinType.JOIN, true)
    }

    infix fun leftJoin(table: String): Select {
        return join(table, SqlJoinType.LEFT_JOIN)
    }

    infix fun leftJoin(table: TableSchema): Select {
        return join(table.tableName, SqlJoinType.LEFT_JOIN)
    }

    infix fun leftJoin(table: SelectQuery): Select {
        return join(table, SqlJoinType.LEFT_JOIN)
    }

    infix fun leftJoinLateral(table: SelectQuery): Select {
        return join(table, SqlJoinType.LEFT_JOIN, true)
    }

    infix fun rightJoin(table: String): Select {
        return join(table, SqlJoinType.RIGHT_JOIN)
    }

    infix fun rightJoin(table: TableSchema): Select {
        return join(table.tableName, SqlJoinType.RIGHT_JOIN)
    }

    infix fun rightJoin(table: SelectQuery): Select {
        return join(table, SqlJoinType.RIGHT_JOIN)
    }

    infix fun righJoinLateral(table: SelectQuery): Select {
        return join(table, SqlJoinType.RIGHT_JOIN, true)
    }

    infix fun innerJoin(table: String): Select {
        return join(table, SqlJoinType.INNER_JOIN)
    }

    infix fun innerJoin(table: TableSchema): Select {
        return join(table.tableName, SqlJoinType.INNER_JOIN)
    }

    infix fun innerJoin(table: SelectQuery): Select {
        return join(table, SqlJoinType.INNER_JOIN)
    }

    infix fun innerJoinLateral(table: SelectQuery): Select {
        return join(table, SqlJoinType.INNER_JOIN, true)
    }

    infix fun crossJoin(table: String): Select {
        return join(table, SqlJoinType.CROSS_JOIN)
    }

    infix fun crossJoin(table: TableSchema): Select {
        return join(table.tableName, SqlJoinType.CROSS_JOIN)
    }

    infix fun crossJoin(table: SelectQuery): Select {
        return join(table, SqlJoinType.CROSS_JOIN)
    }

    infix fun crossJoinLateral(table: SelectQuery): Select {
        return join(table, SqlJoinType.CROSS_JOIN, true)
    }

    infix fun fullJoin(table: String): Select {
        return join(table, SqlJoinType.FULL_JOIN)
    }

    infix fun fullJoin(table: TableSchema): Select {
        return join(table.tableName, SqlJoinType.FULL_JOIN)
    }

    infix fun fullJoin(table: SelectQuery): Select {
        return join(table, SqlJoinType.FULL_JOIN)
    }

    infix fun fullJoinLateral(table: SelectQuery): Select {
        return join(table, SqlJoinType.FULL_JOIN, true)
    }

    fun forUpdate(): Select {
        checkOLAP(this.db)

        this.sqlSelect.forUpdate = true
        return this
    }

    override fun sql(): String {
        if (sqlSelect.selectList.isEmpty()) {
            select()
        }

        return toSqlString(sqlSelect, db)
    }

    fun fetchCountSql(): String {
        val selectCopy = this.sqlSelect.copy(limit = null)
        selectCopy.selectList.clear()
        selectCopy.orderBy.clear()
        selectCopy.addSelectItem(getQueryExpr(count(), this.db).expr, "count")

        return toSqlString(selectCopy, db)
    }

    fun pageSql(pageSize: Int, pageNumber: Int): String {
        if (sqlSelect.selectList.isEmpty()) {
            select()
        }

        val offset = if (pageNumber <= 1) {
            0
        } else {
            pageSize * (pageNumber - 1)
        }
        val limit = SqlLimit(pageSize, offset)

        val selectCopy = getSqlSelect().copy(limit = limit)

        return toSqlString(selectCopy, db)
    }

    override fun getSelect(): SqlSelectQuery {
        return this.sqlSelect
    }

    override fun getDbType(): DB {
        return this.db
    }

    fun <T> find(bind: (List<Map<String, Any?>>) -> List<T>): T? {
        val limit = SqlLimit(1, sqlSelect.limit?.offset ?: 0)
        val selectCopy = this.sqlSelect.copy(limit = limit)
        val sql = toSqlString(selectCopy, db)

        val list = query(sql, isTransaction, conn!!, dbConnection)

        if (list.isEmpty()) {
            return null
        }

        val bindList = bind(list)

        return bindList[0]
    }

    fun <T : Any> find(clazz: Class<T>): T? {
        return find { bind(clazz, it) }
    }

    inline fun <reified T> find(): T? {
        return find { bind(it) }
    }

    fun findMap(): Map<String, Any?>? {
        return find { it }
    }

    override fun fetchCount(): Long {
        val selectCopy = this.sqlSelect.copy(limit = null)
        selectCopy.selectList.clear()
        selectCopy.orderBy.clear()
        selectCopy.addSelectItem(getQueryExpr(count(), this.db).expr, "count")

        val sql = toSqlString(selectCopy, db)

        val result = query(sql, isTransaction, conn!!, dbConnection)

        return result[0]["count"] as Long
    }

    fun <T> page(
        pageSize: Int,
        pageNumber: Int,
        needCount: Boolean = true,
        bind: (List<Map<String, Any?>>) -> List<T>
    ): Page<T> {
        val data = if (pageSize == 0) {
            listOf()
        } else {
            val offset = if (pageNumber <= 1) {
                0
            } else {
                pageSize * (pageNumber - 1)
            }
            val limit = SqlLimit(pageSize, offset)

            val selectCopy = getSqlSelect().copy(limit = limit)

            val sql = toSqlString(selectCopy, db)

            val list = query(sql, isTransaction, conn!!, dbConnection)
            bind(list)
        }

        val totalCount = if (needCount) {
            fetchCount()
        } else {
            0L
        }

        val totalPage = if (totalCount == 0L || pageSize == 0) {
            0L
        } else {
            if (totalCount % pageSize == 0L) {
                totalCount / pageSize
            } else {
                totalCount / pageSize + 1
            }
        }

        return Page(totalPage, totalCount, data)
    }

    fun <T : Any> page(clazz: Class<T>, pageSize: Int, pageNumber: Int, needCount: Boolean = true): Page<T> {
        return page(pageSize, pageNumber, needCount) { bind(clazz, it) }
    }

    inline fun <reified T> page(pageSize: Int, pageNumber: Int, needCount: Boolean = true): Page<T> {
        return page(pageSize, pageNumber, needCount) { bind(it) }
    }

    fun pageMap(pageSize: Int, pageNumber: Int, needCount: Boolean = true): Page<Map<String, Any?>> {
        return page(pageSize, pageNumber, needCount) { it }
    }

    private fun turnPage(result: List<Map<String, Any?>>, sort: OrderBy): List<Map<String, Any?>> {
        val sortName = when (sort.query) {
            is QueryColumn -> sort.query.column
            is QueryTableColumn -> sort.query.column
            else -> throw SQLException("此分页方式的排序需要使用列名")
        }

        val sortList = result.map {
            val sortItem = when (val s = it[sortName]) {
                is Int -> s
                is Long -> s
                is Short -> s
                is Byte -> s
                is Char -> s
                is Float -> s
                is Double -> s
                is BigDecimal -> s
                is String -> s
                is Boolean -> s
                is Date -> s
                else -> throw TypeCastException("字段类型不可用来排序")
            }

            sortName to sortItem
        }

        val sortedList = if (sort.order == SqlOrderByOption.ASC) {
            sortList.sortedWith(compareBy { it.second })
        } else {
            sortList.sortedWith(compareByDescending { it.second })
        }

        return sortedList.map {
            val filterList = result.filter { item -> item[sortName] == it.second }
            filterList[0]
        }
    }

    fun <T> firstPage(sort: OrderBy, pageSize: Int, bind: (List<Map<String, Any?>>) -> List<T>): List<T> {
        val selectCopy = getSqlSelect().copy()
        selectCopy.orderBy.clear()
        selectCopy.orderBy.add(SqlOrderBy(getQueryExpr(sort.query, this.db).expr, sort.order))
        selectCopy.limit = SqlLimit(pageSize, 0)
        val list = query(toSqlString(selectCopy, db), isTransaction, conn!!, dbConnection)
        return bind(list)
    }

    fun <T : Any> firstPage(clazz: Class<T>, sort: OrderBy, pageSize: Int): List<T> {
        return firstPage(sort, pageSize) { bind(clazz, it) }
    }

    inline fun <reified T> firstPage(sort: OrderBy, pageSize: Int): List<T> {
        return firstPage(sort, pageSize) { bind(it) }
    }

    fun firstPageMap(sort: OrderBy, pageSize: Int): List<Map<String, Any?>> {
        return firstPage(sort, pageSize) { it }
    }

    fun <T> lastPage(sort: OrderBy, pageSize: Int, bind: (List<Map<String, Any?>>) -> List<T>): List<T> {
        val selectCopy = getSqlSelect().copy()
        selectCopy.orderBy.clear()
        selectCopy.orderBy.add(SqlOrderBy(getQueryExpr(sort.query, this.db).expr, sort.order.turn()))
        selectCopy.limit = SqlLimit(pageSize, 0)
        val list = query(toSqlString(selectCopy, db), isTransaction, conn!!, dbConnection)

        if (list.isEmpty()) {
            return listOf()
        }

        return bind(turnPage(list, sort))
    }

    fun <T : Any> lastPage(clazz: Class<T>, sort: OrderBy, pageSize: Int): List<T> {
        return lastPage(sort, pageSize) { bind(clazz, it) }
    }

    inline fun <reified T> lastPage(sort: OrderBy, pageSize: Int): List<T> {
        return lastPage(sort, pageSize) { bind(it) }
    }

    fun lastPageMap(sort: OrderBy, pageSize: Int): List<Map<String, Any?>> {
        return lastPage(sort, pageSize) { it }
    }

    fun <T> previousPage(
        sort: OrderBy,
        value: Any,
        pageSize: Int,
        bind: (List<Map<String, Any?>>) -> List<T>
    ): List<T> {
        val selectCopy = getSqlSelect().copy()
        selectCopy.orderBy.clear()
        selectCopy.orderBy.add(SqlOrderBy(getQueryExpr(sort.query, this.db).expr, sort.order.turn()))
        selectCopy.limit = SqlLimit(pageSize, 0)
        val operator = if (sort.order == SqlOrderByOption.ASC) {
            SqlBinaryOperator.LT
        } else {
            SqlBinaryOperator.GT
        }
        selectCopy.addCondition(SqlBinaryExpr(getQueryExpr(sort.query, this.db).expr, operator, getExpr(value)))
        val list = query(toSqlString(selectCopy, db), isTransaction, conn!!, dbConnection)

        if (list.isEmpty()) {
            return listOf()
        }

        return bind(turnPage(list, sort))
    }

    fun <T : Any> previousPage(clazz: Class<T>, sort: OrderBy, value: Any, pageSize: Int): List<T> {
        return previousPage(sort, value, pageSize) { bind(clazz, it) }
    }

    inline fun <reified T> previousPage(sort: OrderBy, value: Any, pageSize: Int): List<T> {
        return previousPage(sort, value, pageSize) { bind(it) }
    }

    fun previousPageMap(sort: OrderBy, value: Any, pageSize: Int): List<Map<String, Any?>> {
        return previousPage(sort, value, pageSize) { it }
    }

    fun <T> nextPage(sort: OrderBy, value: Any, pageSize: Int, bind: (List<Map<String, Any?>>) -> List<T>): List<T> {
        val selectCopy = getSqlSelect().copy()
        selectCopy.orderBy.clear()
        selectCopy.orderBy.add(SqlOrderBy(getQueryExpr(sort.query, this.db).expr, sort.order))
        selectCopy.limit = SqlLimit(pageSize, 0)
        val operator = if (sort.order == SqlOrderByOption.ASC) {
            SqlBinaryOperator.GT
        } else {
            SqlBinaryOperator.LT
        }
        selectCopy.addCondition(SqlBinaryExpr(getQueryExpr(sort.query, this.db).expr, operator, getExpr(value)))
        val list = query(toSqlString(selectCopy, db), isTransaction, conn!!, dbConnection)
        return bind(list)
    }

    fun <T : Any> nextPage(clazz: Class<T>, sort: OrderBy, value: Any, pageSize: Int): List<T> {
        return nextPage(sort, value, pageSize) { bind(clazz, it) }
    }

    inline fun <reified T> nextPage(sort: OrderBy, value: Any, pageSize: Int): List<T> {
        return nextPage(sort, value, pageSize) { bind(it) }
    }

    fun nextPageMap(sort: OrderBy, value: Any, pageSize: Int): List<Map<String, Any?>> {
        return nextPage(sort, value, pageSize) { it }
    }
}